#!/usr/bin/python
# -*- coding: utf-8 -*-


import DB2
import urllib
from parsers import *

DEBUG = 1

class DbEngineDB2:

        conn = False
                
        def __init__(self, hostStr):
                self.users = []
                self.connect()

        def connect(self):
                self.conn = DB2.connect(dsn='wykopdat', uid='sppi', pwd='sppi')

        def disconnect(self):
                self.conn.close()
                self.conn = False

        def executeQuery(self, query, p):
                if self.conn:
                        curs = self.conn.cursor()
                        curs.execute(query)
                        if p == 1:
                            res = curs.fetchall()
                        else:
                            res = True        
                        curs.close()
                        self.conn.commit()
                        return res
                return False
        
        def containsFind(self, linkFindStr):
                """ Funkcja zwraca 1 jeśli w bazie jest już znalezisko o zadanym linku, 0 wpp.
                argumenty:
                linkFindStr -- link do znaleziska
                """
                query = "SELECT ID FROM Wykop WHERE Link ='" + linkFindStr + "'"

                if DEBUG:
                        print "COntFind Query" + query
                res = self.executeQuery(query,1)
                if DEBUG:
                        print "ContFind res" + str(res)

                if res:
                        res = res[0][0]
                else:
                        res = 0
                return res

        def addFind(self, find, linkFindStr):
                """ Dodaje znalezisko do bazy.

                argumenty:
                find -- typ: Wykop
                linkFindSTR -- typ: String"""
                
                print 'dodano znalezisko o tytule "', find.getTitle(), '"'
                
                usr = self.containsUser(find.getUser())
                if find.getPublished():
                        query = "INSERT INTO WYKOP (USER_ID, SOURCE_LINK, ADDED, PUBLISHED, CATEGORY, LINK, TITLE) "
                        query += "VALUES (" + str(usr) + ",'" + find.getSource() + "','" + find.getAdded().isoformat(' ')
                        query += "','" + find.getPublished().isoformat(' ') + "','" + find.getCategory() + "','" + linkFindStr + "','" + find.getTitle() + "')"
                else:
                        query = "INSERT INTO WYKOP (USER_ID, SOURCE_LINK, ADDED, CATEGORY, LINK, TITLE) "
                        query += "VALUES (" + str(usr) + ",'" + find.getSource() + "','" + find.getAdded().isoformat(' ')
                        query += "','" + find.getCategory() + "','" + linkFindStr + "','" + find.getTitle() + "')"

                if DEBUG:
                        print "Find Query " + query
                        
                res = self.executeQuery(query,0)
                
                if DEBUG:
                        print "Find Res " + str(res)
                        
                tags = find.getTags()

                for tag in tags:
                        tagID = self.containsTag(tag)
                        findID = self.containsFind(linkFindStr)
                        if tagID:
                                if not self.containsFindHasTag(tagID, findID):
                                        self.addFindHasTag(tagID, findID)
                        else:
                                self.addTag(tag)
                                tagID = self.containsTag(tag)
                                self.addFindHasTag(tagID, findID)
                                
        
        def updateFind(self, find):
                """ akualizuje dane znaleziska (głównie czy opublikowano je czy nie)"""

                if find.getPublished():
                        query = "UPDATE WYKOP SET PUBLISHED ='" + find.getPublished().isoformat(' ') + "' WHERE LINK = '" + find.getLink() + "'"
                        self.executeQuery(query,0)

        def isPublished(self, linkFindStr):
                """Sprawdza czy dane znalezisko zostało opublikowanie"""

                query = "SELECT PUBLISHED FROM WYKOP WHERE LINK = '" + linkFindStr + "'"
                pub = self.executeQuery(query, 1)
                if pub:
                        return True
                else:
                        return False

        def getFind(self, linkFindStr):
                """ Funkcja zwraca obiekt klasy Wykop"""

                query = "SELECT * FROM WYKOP WHERE LINK ='" + linkFindStr + "'"
                res = self.executeQuery(query,1)[0]
                print "getFind res"
                print res
                find = Wykop()
                find.setId(res[0])
                find.setSource(res[2])
                find.setAdded(datetime.strptime(res[3][0:len(res[3])-7],"%Y-%m-%d-%H.%M.%S"))
                if res[4]:
                        find.setAdded(datetime.strptime(res[4][0:len(res[4])-7],"%Y-%m-%d-%H.%M.%S"))
                find.setCategory(res[5])
                find.setLink(linkFindStr)
                find.setLink(res[7])
                
                return Wykop()

        def addComment(self, linkFindStr, comment):
                """ Dodaje komentarz do bazy,

                argumenty:
                linkFindStr -- link do skomentowanego znaleziska
                comment  -- obiekt typu Comment"""
                print 'dodano komentarz użytkownika ', comment.getUser(), ' o treści "',comment.getText() , '"'

                findID = self.containsFind(linkFindStr)
                userID = self.containsUser(comment.getUser())

                query = "INSERT INTO USER_COMMENTED_WYKOP (USER_ID,WYKOP_ID,DATE,TEXT) VALUES(" + str(userID)+ "," + str(findID) + ",'" + comment.getDate().isoformat(' ') + "','" + self.stripslash(comment.getText()) +"')"
                res = self.executeQuery(query,0)

                return res
                
        def containsComment(self, linkFindStr, comment):
                """ Sprawdza, czy dany komentarz znajduje się już w bazie"""

                userID = self.containsUser(comment.getUser())
                findID = self.containsFind(linkFindStr)

                query = "SELECT COUNT(*) FROM USER_COMMENTED_WYKOP WHERE USER_ID =" + str(userID) + " AND WYKOP_ID =" + str(findID) + " AND DATE ='" + comment.getDate().isoformat(' ') + "'" 
                res = self.executeQuery(query,1)
                
                return res[0][0]
                       

        def addUser(self, user, linkUserStr):
                """ Dodaje użytkownika do bazy

                argumenty:
                user -- obiekt klasy user"""
                print 'dodano użytkownika', user.getNick()

                if user.getRegistered():
                        date = user.getRegistered()
                else:
                        date = datetime(1980,1,1)

                query = "INSERT INTO USER_DATA(NICK, ACCOUNTCREATED ,LINK) VALUES('" + user.getNick() + "','" + date.isoformat(' ') + "','" + linkUserStr + "')"
                if DEBUG:
                        print "AddUser query" + query
                res = self.executeQuery(query,0)
##                self.users.append(user.getNick())
                return res

        def containsUser(self, linkUserStr):
                """ Sprawdza czy baza zawiera użytkownika"""
                query = "SELECT ID FROM USER_DATA WHERE Link ='" + linkUserStr + "'"
                print "contUser query" + query
                res = self.executeQuery(query,1)
                if res:
                        res = res[0][0]
                else:
                        res = 0

                return res #tu należy sprawdzić jak wygląda wynik i pogadać z lisem
                
##                print self.users
##                if linkUserStr.split("/")[-1] not in self.users: return 0
##                else: return 1

        def addLink(self, linkFindStr, link):
                """Dodaje powiązany link do znaleziska"""
                print "dodano powiązany link ", linkFindStr

                findID = self.containsFind(linkFindStr)
                linkID = self.containsLinkInst(link)

                if not linkID:
                        self.addLinkInst(link)
                        linkID = self.containsLinkInst(link)
                        
                if link.getAutor():
                        userID = self.containsUser(link.getAutor())
                        query = "INSERT INTO WYKOP_HAS_LINK(WYKOP_ID,LINK_ID,AUTHOR) VALUES (" + str(findID) + "," + str(linkID) + "," + str(userID) + ")"
                else:
                        query = "INSERT INTO WYKOP_HAS_LINK(WYKOP_ID,LINK_ID,GUESTS) VALUES (" + str(findID) + "," + str(linkID) + "," + str(link.getGuests()) + ")"

                res = self.executeQuery(query,0)
                return res

        def containsLink(self, linkFindStr, link):
                """Sprawdza czy dany link jest powiązany z danym znaleziskiem"""

                findID = self.containsFind(linkFindStr)
                linkID = self.containsLinkInst(link)

                if not linkID:
                        return 0
                else:
                        query = "SELECT COUNT(*) FROM WYKOP_HAS_LINK WHERE WYKOP_ID = " + str(findID) + " AND LINK_ID = " + str(linkID)
                        res = self.executeQuery(query,1)
                        if DEBUG:
                                print "contLink query " + query
                                print "contLink res" + str(res)

                        return res[0][0]

        def addLinkInst(self, link):
                """Dodaje link """
                print "dodano link ", link.getLink()

                query = "INSERT INTO LINKS(LINK) VALUES ('" + link.getLink() + "')"
                res = self.executeQuery(query,0)

                return res

        def containsLinkInst(self, link):
                """Sprawdza czy dany link istnieje"""

                query = "SELECT ID FROM LINKS WHERE LINK = '" + link.getLink() + "'"
                res = self.executeQuery(query,1)

                if DEBUG:
                        print "contLinkI res" + str(res)
                
                if res:
                        res = res[0][0]
                else:
                        res = 0
                return res

        def addWykopali(self, linkFindStr, wykopali):
                """ Funkcja aktualizuje listę użytkowników, którzy wykopali

                argumenty
                wykopali -- obiekt klasy Wykopali"""

                findID = self.containsFind(linkFindStr)
                userID = self.containsUser(wykopali.getUser())

                rank = wykopali.getUserRank()

                if rank:
                        query = "INSERT INTO USER_RATED_WYKOP (USER_ID, WYKOP_ID, NOTE, USER_RANK, DATE) VALUES (" + str(userID) + "," + str(findID) + "," + str(wykopali.getNote()) + "," + str(rank) + ",'" + wykopali.getDate().isoformat(' ') + "')"
                else:
                        query = "INSERT INTO USER_RATED_WYKOP (USER_ID, WYKOP_ID, NOTE, DATE) VALUES (" + str(userID) + "," + str(findID) + "," + str(wykopali.getNote()) + ",'" + wykopali.getDate().isoformat(' ') + "')"

                if DEBUG:
                        print "addWyk query " + query
                res = self.executeQuery(query,0)
                return res
        
        def containsWykopali(self, linkFindStr, wykopali):
                """Sprawdza czy dany "wykopali" (?!) wykopał znalezisko"""
                return self.containsRate(linkFindStr, wykopali)

        def containsRate(self, linkFindStr, rate):

                findID = self.containsFind(linkFindStr)
                userID = self.containsUser(rate.getUser())

                query = "SELECT COUNT(*) FROM USER_RATED_WYKOP WHERE USER_ID =" + str(userID) + " AND WYKOP_ID =" + str(findID)
                res = self.executeQuery(query,1)

                return res[0][0]

        def addZakopali(self, linkFindStr, zakopali):
                """ """
                findID = self.containsFind(linkFindStr)
                userID = self.containsUser(zakopali.getUser())

                rank = zakopali.getUserRank()

                if rank:
                        query = "INSERT INTO USER_RATED_WYKOP (USER_ID,WYKOP_ID,NOTE,REASON,USER_RANK,DATE) VALUES (" + str(userID) + "," + str(findID) + "," + str(zakopali.getNote()) + ",'" + zakopali.getReason() + "'," + str(rank) + ",'" + zakopali.getDate().isoformat(' ') + "')"
                else:
                        query = "INSERT INTO USER_RATED_WYKOP (USER_ID,WYKOP_ID,NOTE,REASON,DATE) VALUES (" + str(userID) + "," + str(findID) + "," + str(zakopali.getNote()) + ",'" + zakopali.getReason() + "','" + zakopali.getDate().isoformat(' ') + "')"

                res = self.executeQuery(query,0)
                return res 

        def containsZakopali(self, linkFindStr, zakopali):
                """ """
                return self.containsRate(linkFindStr, zakopali)

        def addTag(self, tagStr):
                """Dodaje tag do bazy i zwraca jego ID"""
                
                query = "INSERT INTO TAGS(TAG) VALUES('" + tagStr + "')"
                res = self.executeQuery(query,0)

                return res #tu należy sprawdzić jak wygląda wynik

        def containsTag(self, tagStr):
                """Sprawdza czy dany tag jest w bazie, jeżeli tak, zwracany jest
                jego ID w przeciwnym wypadku -1"""

                query = "SELECT ID FROM TAGS WHERE Tag = '" + tagStr + "'"
                res = self.executeQuery(query,1)

                if DEBUG:
                        print "contTag res" + str(res)
                
                if res:
                        res = res[0][0]
                else:
                        res = 0
                return res 

        def containsFindHasTag(self, tagID, findID): 
                """Sprawdza czy danemu znalezisku przypisany jest tag"""

                query = "SELECT COUNT(*) FROM WYKOP_HAS_TAG WHERE TAG_ID = " + str(tagID) + " AND Wykop_ID = " + str(findID)
                res = self.executeQuery(query,1)

                if DEBUG:
                        print "contFhT res" + str(res)
                
                return res[0][0]

        def addFindHasTag(self, tagID, findID):
                """Dodaje połączenie znalezisko-tag"""

                query = "INSERT INTO WYKOP_HAS_TAG(TAG_ID,WYKOP_ID) VALUES(" + str(tagID) + "," + str(findID) + ")"

                if DEBUG:
                        print "addFhT query" + str(query)

                res = self.executeQuery(query,0)
                return res

        def stripslash(self, string):
                return string.replace("'", '"')


db2 = DbEngineDB2("as")
print db2.isPublished('http://wiadomosci.wp.pl/kat,1342,title,Rada-Nadzorcza-TVP-ustawila-konkurs-na-prezesa-telewizji,wid,11762940,wiadomosc.html?ticaid=19403')
            
